
import java.sql.*;

public class DBConnection {

    //    驱动类名
    String driver="com.mysql.cj.jdbc.Driver";
    String url="jdbc:mysql://localhost:3306/1?serverTimezone=GMT";
    String user="root";
    String password="wang19991014";
    Connection  coon=null;
    public DBConnection(){
        try{
            //    加载驱动程序
            Class.forName(driver);
            coon=(Connection)DriverManager.getConnection(url,user,password);
            if(!coon.isClosed()){
                System.out.println("成功连接数据库！");
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    public void close(){
        try{
            this.coon.close();
        }catch(Exception e){
            e.printStackTrace();
        }
    }

    //    增加数据

    public  void addUser(String Student_id, String Sname, int Sage, String Ssex, String Dept_id, String Sclass){
        String sql="insert into student(Student_id,Sname,Sage,Ssex,Dept_id,Sclass) values('"+Student_id+"','"+Sname+"','"+Sage+"','"+Ssex+"','"+Dept_id+"','"+Sclass+"')";
        try{
            PreparedStatement preStmt=(PreparedStatement)this.coon.prepareStatement(sql);
            preStmt.executeUpdate();
            System.out.println("插入数据成功！");
            preStmt.close();
        }catch(Exception e){
            e.printStackTrace();
        }
    }

    //    查询
    public void select(){
        String sql="select * from student";  //查询usrInfo表中的信息

        try{
            Statement stmt=(Statement)this.coon.createStatement();
            ResultSet rs=(ResultSet)stmt.executeQuery(sql);  //得到的是结果的集合
            System.out.println("--------------------------------");
            System.out.println("学号"+"\t"+"\t"+"\t"+"\t"+"姓名"+"\t"+"\t"+"年龄"+"\t"+"性别"+"\t"+"学院"+"\t"+"课程");
            System.out.println("--------------------------------");
            while(rs.next()){
                String Student_id=rs.getString("Student_id");
                String Sname=rs.getString("Sname");
                int Sage=rs.getInt("Sage");
                String Ssex=rs.getString("Ssex");
                String Dept_id=rs.getString("Dept_id");
                String Sclass =rs.getString("Sclass");
                System.out.println(Student_id+"\t"+Sname+"\t"+Sage+"\t"+Ssex+"\t"+Dept_id+"\t"+Sclass);
            }
            stmt.close();
        }catch(Exception e){
            e.printStackTrace();
        }
    }

//    更改数据
public void update(String Student_id, String Sname, int Sage, String Ssex, String Dept_id, String Sclass){
    String sql="update student set Sname=?,Sage=?,Ssex=?,Dept_id=?,Sclass=? where Student_id=?";
    try{
        PreparedStatement prestmt=(PreparedStatement)this.coon.prepareStatement(sql);
        prestmt.setString(1,Sname);
        prestmt.setString(2, String.valueOf(Sage));
        prestmt.setString(3,Ssex);
        prestmt.setString(4,Dept_id);
        prestmt.setString(5,Sclass);
        prestmt.setString(6,Student_id);
        prestmt.executeUpdate();
        System.out.println("更改数据成功！");
        prestmt.close();
    }catch(Exception e){
        e.printStackTrace();
    }
}

    //    删除数据
    public void del(String Student_id){
        String sql="delete from student where Student_id=?";
        try{
            PreparedStatement prestmt=(PreparedStatement)this.coon.prepareStatement(sql);
            prestmt.setString(1, Student_id);
            prestmt.executeUpdate();
            System.out.println("删除数据成功！");
            prestmt.close();
        }catch(Exception e){
            e.printStackTrace();
        }
    }
}
